package com.skaz.dao.jpa.support;

import com.skaz.Constants;
import com.skaz.bean.Page;
import com.skaz.dao.jpa.util.Sqls;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author jungle
 */
@Data
@Component
public class JdbcSupport {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    //
    // get by sql with entityClass
    // ---------------------------------------------------------------------------------------------------
    public <E> E get(String sql, Class<E> entityClass) {
        List<E> es = this.jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(entityClass));
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, int size) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass));
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, Map<String, Object> params) {
        List<E> es = this.namedParameterJdbcTemplate.query(sql, params, BeanPropertyRowMapper.newInstance(entityClass));
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, int size, Map<String, Object> params) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass), params);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, List<Object> params) {
        List<E> es = this.jdbcTemplate.query(sql, params.toArray(), BeanPropertyRowMapper.newInstance(entityClass));
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, int size, List<Object> params) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass), params.toArray());
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, Object... params) {
        List<E> es = this.jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(entityClass), params);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, Class<E> entityClass, int size, Object... params) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass), params);
        return es.size() > 0 ? es.get(0) : null;
    }

    //
    // find by sql with entityClass
    // ---------------------------------------------------------------------------------------------------
    public <E> List<E> list(String sql, Class<E> entityClass) {
        return this.jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(entityClass));
    }

    public <E> List<E> list(String sql, Class<E> entityClass, int size) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass));
    }

    public <E> List<E> list(String sql, Class<E> entityClass, Map<String, Object> params) {
        return this.namedParameterJdbcTemplate.query(sql, params, BeanPropertyRowMapper.newInstance(entityClass));
    }

    public <E> List<E> list(String sql, Class<E> entityClass, int size, Map<String, Object> params) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass), params);
    }

    public <E> List<E> list(String sql, Class<E> entityClass, List<Object> params) {
        return this.jdbcTemplate.query(sql, params.toArray(), BeanPropertyRowMapper.newInstance(entityClass));
    }

    public <E> List<E> list(String sql, Class<E> entityClass, int size, List<Object> params) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass), params.toArray());
    }

    public <E> List<E> list(String sql, Class<E> entityClass, Object... params) {
        return this.jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(entityClass), params);
    }

    public <E> List<E> list(String sql, Class<E> entityClass, int size, Object... params) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), BeanPropertyRowMapper.newInstance(entityClass), params);
    }

    //
    // page by sql with entityClass
    // ---------------------------------------------------------------------------------------------------
    public <E> Page<E> page(String sql, Class<E> entityClass, int no, int size) {
        return this.page(sql, entityClass, no, size, new HashMap<>());
    }

    public <E> Page<E> page(String sql, Class<E> entityClass, int no, int size, List<Object> params) {
        return this.page(sql, entityClass, no, size, params.toArray());
    }

    public <E> Page<E> page(String sql, Class<E> entityClass, int no, int size, Map<String, Object> params) {
        boolean hasCount = size > 0;
        if (no < 1) {
            no = 1;
        }
        if (size == 0) {
            size = Constants.PAGE_SIZE;// or throw new IllegalArgumentException("参数错误:pageSize不能为0.");
        }
        int pageNo = no - 1;
        int pageSize = Math.abs(size);
        long total = 0;
        List<E> entities = new ArrayList<>();
        if (hasCount) {
            total = this.count(sql, params);
            if (total > 0) {
                entities = this.list(Sqls.buildPageSql(sql, pageNo, pageSize, hasCount), entityClass, params);
            }
        } else {
            int expectedSize = pageSize + 1;// 多查一条数据以判断是否有下一页
            entities = this.list(Sqls.buildPageSql(sql, pageNo, expectedSize, hasCount), entityClass, params);
            int contentSize = entities.size();
            if (contentSize == expectedSize) {
                entities.remove(entities.size() - 1);// 去掉多查询的一条结果
            }
            total = contentSize > 0 ? (pageNo * pageSize) + contentSize : 0;// 若未查询到结果,则总数为0;
        }
        return new Page<>(entities, no, size, total);// 可传递正负数,以让Page对象区分是否为分页查询.
    }

    public <E> Page<E> page(String sql, Class<E> entityClass, int no, int size, Object... params) {
        boolean hasCount = size > 0;
        if (no < 1) {
            no = 1;
        }
        if (size == 0) {
            size = Constants.PAGE_SIZE;// or throw new IllegalArgumentException("参数错误:pageSize不能为0.");
        }
        int pageNo = no - 1;
        int pageSize = Math.abs(size);
        long total = 0;
        List<E> entities = new ArrayList<>();
        if (hasCount) {
            total = this.count(sql, params);
            if (total > 0) {
                entities = this.list(Sqls.buildPageSql(sql, pageNo, pageSize, hasCount), entityClass, params);
            }
        } else {
            int expectedSize = pageSize + 1;
            entities = this.list(Sqls.buildPageSql(sql, pageNo, expectedSize, hasCount), entityClass, params);
            int contentSize = entities.size();
            if (contentSize == expectedSize) {
                entities.remove(entities.size() - 1);
            }
            total = contentSize > 0 ? (pageNo * pageSize) + contentSize : 0;
        }
        return new Page<>(entities, no, size, total);
    }

    //
    // get by sql with RowMapper
    // ---------------------------------------------------------------------------------------------------
    public <E> E get(String sql, RowMapper<E> mapper) {
        List<E> es = this.jdbcTemplate.query(sql, mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, int size) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, Map<String, Object> params) {
        List<E> es = this.namedParameterJdbcTemplate.query(sql, params, mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, int size, Map<String, Object> params) {
        List<E> es = this.namedParameterJdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), params, mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, List<Object> params) {
        List<E> es = this.jdbcTemplate.query(sql, params.toArray(), mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, int size, List<Object> params) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), params.toArray(), mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, Object... params) {
        List<E> es = this.jdbcTemplate.query(sql, mapper, params);
        return es.size() > 0 ? es.get(0) : null;
    }

    public <E> E get(String sql, RowMapper<E> mapper, int size, Object... params) {
        List<E> es = this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), params, mapper);
        return es.size() > 0 ? es.get(0) : null;
    }

    //
    // find by sql with RowMapper
    // ---------------------------------------------------------------------------------------------------
    public <E> List<E> list(String sql, RowMapper<E> mapper) {
        return this.jdbcTemplate.query(sql, mapper);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, int size) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), mapper);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, Map<String, Object> params) {
        return this.namedParameterJdbcTemplate.query(sql, params, mapper);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, int size, Map<String, Object> params) {
        return this.namedParameterJdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), params, mapper);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, List<Object> params) {
        return this.jdbcTemplate.query(sql, params.toArray(), mapper);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, int size, List<Object> params) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), params.toArray(), mapper);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, Object... params) {
        return this.jdbcTemplate.query(sql, mapper, params);
    }

    public <E> List<E> list(String sql, RowMapper<E> mapper, int size, Object... params) {
        return this.jdbcTemplate.query(Sqls.buildPageSql(sql, 1, size), params, mapper);
    }

    //
    // page by sql with RowMapper
    // ---------------------------------------------------------------------------------------------------
    public <E> Page<E> page(String sql, RowMapper<E> mapper, int no, int size) {
        return this.page(sql, mapper, no, size, new HashMap<>());
    }

    public <E> Page<E> page(String sql, RowMapper<E> mapper, int no, int size, List<Object> params) {
        return this.page(sql, mapper, no, size, params.toArray());
    }

    public <E> Page<E> page(String sql, RowMapper<E> mapper, int no, int size, Map<String, Object> params) {
        boolean hasCount = size > 0;
        if (no < 1) {
            no = 1;
        }
        if (size == 0) {
            size = Constants.PAGE_SIZE;// or throw new IllegalArgumentException("参数错误:pageSize不能为0.");
        }
        int pageNo = no - 1;
        int pageSize = Math.abs(size);
        long total = 0;
        List<E> entities = new ArrayList<>();
        if (hasCount) {
            total = this.count(sql, params);
            if (total > 0) {
                entities = this.list(Sqls.buildPageSql(sql, pageNo, pageSize, hasCount), mapper, params);
            }
        } else {
            int expectedSize = pageSize + 1;
            entities = this.list(Sqls.buildPageSql(sql, pageNo, expectedSize, hasCount), mapper, params);
            int contentSize = entities.size();
            if (contentSize == expectedSize) {
                entities.remove(entities.size() - 1);
            }
            total = contentSize > 0 ? (pageNo * pageSize) + contentSize : 0;
        }
        return new Page<>(entities, pageNo, size, total);
    }

    public <E> Page<E> page(String sql, RowMapper<E> mapper, int no, int size, Object... params) {
        boolean hasCount = size > 0;
        if (no < 1) {
            no = 1;
        }
        if (size == 0) {
            size = Constants.PAGE_SIZE;// or throw new IllegalArgumentException("参数错误:pageSize不能为0.");
        }
        int pageNo = no - 1;
        int pageSize = Math.abs(size);
        long total = 0;
        List<E> entities = new ArrayList<>();
        if (hasCount) {
            total = this.count(sql, params);
            if (total > 0) {
                entities = this.list(Sqls.buildPageSql(sql, pageNo, pageSize, hasCount), mapper, params);
            }
        } else {
            int expectedSize = pageSize + 1;
            entities = this.list(Sqls.buildPageSql(sql, pageNo, expectedSize, hasCount), mapper, params);
            int contentSize = entities.size();
            if (contentSize == expectedSize) {
                entities.remove(entities.size() - 1);
            }
            total = contentSize > 0 ? (pageNo * pageSize) + contentSize : 0;
        }
        return new Page<>(entities, no, size, total);
    }


    //
    // count
    // ---------------------------------------------------------------------------------------------------
    public long count(String sql) {
        return this.jdbcTemplate.queryForObject(Sqls.buildCountSql(sql), long.class);
    }

    public long count(String sql, List<Object> params) {
        return this.jdbcTemplate.queryForObject(Sqls.buildCountSql(sql), params.toArray(), long.class);
    }

    public long count(String sql, Object... params) {
        return this.jdbcTemplate.queryForObject(Sqls.buildCountSql(sql), params, long.class);
    }

    public long count(String sql, Map<String, Object> params) {
        return this.namedParameterJdbcTemplate.queryForObject(Sqls.buildCountSql(sql), params, long.class);
    }

    //
    // update
    // ---------------------------------------------------------------------------------------------------
    public int update(String sql, Object... args) {
        return this.jdbcTemplate.update(sql, args);
    }

    public int update(String sql, SqlParameterSource paramSource) {
        return this.namedParameterJdbcTemplate.update(sql, paramSource);
    }

    public int update(final PreparedStatementCreator psc) {
        return this.jdbcTemplate.update(psc);
    }

    public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder) {
        return this.jdbcTemplate.update(psc, generatedKeyHolder);
    }

    //
    // execute
    // ---------------------------------------------------------------------------------------------------
    public void execute(final String sql) {
        this.jdbcTemplate.execute(sql);
    }

    //
    // other
    // ---------------------------------------------------------------------------------------------------
    public <E> List<E> query(String sql, Object[] args, RowMapper<E> rowMapper) {
        return this.jdbcTemplate.query(sql, args, new RowMapperResultSetExtractor<E>(rowMapper));
    }

    //
    // query for List<?>
    // ---------------------------------------------------------------------------------------------------
    public List<String> queryForStringList(String sql, Object... params) {
        return this.jdbcTemplate.queryForList(sql, String.class, params);
    }

    public List<Long> queryForLongList(String sql, Object... params) {
        return this.jdbcTemplate.queryForList(sql, Long.class, params);
    }

    public List<Integer> queryForIntegerList(String sql, Object... params) {
        return this.jdbcTemplate.queryForList(sql, Integer.class, params);
    }

    public DataSource getDataSource() {
        return this.jdbcTemplate.getDataSource();
    }

}
